use log::{debug, info};
use std::result::Result;
use std::{
    error::Error,
    iter::Peekable,
    str::{Chars, FromStr},
};

use super::{Column, DataType, Entry, Insert, Keyword, Select, SelectLiteral, Table, Update, Value, Order};
use crate::CustomError;

#[derive(Debug)]
pub enum Token {
    Select(Select),
    Insert(Insert),
    Update(Update),
    Delete,
    Create(Table),
    Alter,
}

pub struct Lexer<'a> {
    pub code: Peekable<Chars<'a>>,
    pub position: usize,
}

impl<'a> Lexer<'a> {
    pub fn new(sql: &'a str) -> Self {
        Lexer {
            code: sql.trim().chars().peekable(),
            position: 0,
        }
    }

    pub fn parse(&mut self) -> Result<Token, Box<dyn Error>> {
        let keyword = self.next_variable()?;

        let token = match keyword.as_str() {
            "select" => self.parse_select(),
            "insert" => self.parse_insert(),
            "update" => self.parse_update(),
            "delete" => self.parse_delete(),
            "create" => self.parse_create_table(),
            "alter" => self.parse_alter(),
            _ => return Err(CustomError::SqlParseError.into()),
        };

        self.next_is_symbol(';')?;

        debug!("token: {:#?}", token);

        token
    }

    /**
    * CREATE TABLE products (
       product_id INT NOT NULL PRIMARY COMMENT '唯一id',
       name TEXT NOT NULL,
       price FLOAT,
       stock_quantity INT DEFAULT '0'
       );
    */
    pub fn parse_create_table(&mut self) -> Result<Token, Box<dyn Error>> {
        self.next_equal(Keyword::Table.into())?;
        let table_name = self.next_variable()?;
        self.next_is_symbol('(')?;

        let columns = self.parse_table_columns()?;
        let primary_key = Self::find_primary_key(&columns)?;

        let table = Table {
            table_name,
            columns,
            primary_key,
        };

        let token = Token::Create(table);
        Ok(token)
    }

    /**
     * select * from products;
     * select * from products where id = 1 and name = 'phone';
     * select name, price from products group by name order by name asc limit 10 offset 0;
     */
    pub fn parse_select(&mut self) -> Result<Token, Box<dyn Error>> {
        let select_literal = self.parse_select_literal()?;
        self.next_equal(Keyword::From.into())?;
        let table_name = self.next_variable()?;

        let mut select = Select {
            table_name,
            literal: select_literal,
            swhere: None,
            order: None,
            group_by: None,
            limit: None,
            offset: None,
        };

        while let Err(_) = self.peek_is_symbol(';') {
            let variable = self.next_variable()?;
            match variable.as_str() {
                "where" => {
                    select.swhere = self.parse_where()?;
                },
                "order" => {
                    select.order = self.parse_order_by()?;
                },
                "group" => {
                    select.group_by = self.parse_group_by()?;
                },
                "limit" => {
                    if let Ok(Some((limit, offset))) = self.parse_limit() {
                        select.limit = Some(limit);
                        select.offset = Some(offset);
                    }
                },
                _ => return Err(CustomError::SqlParseError.into()),
            };
        }

        let token = Token::Select(select);
        Ok(token)
    }

    /**
     * insert into products values (1, 'hello', 3.0, 1);
     * insert into products (product_id, name, price, stock_quantity) values (1, 'hello', 3.0, 1);
     */
    pub fn parse_insert(&mut self) -> Result<Token, Box<dyn Error>> {
        self.next_equal(Keyword::Into.into())?;
        let table_name = self.next_variable()?;

        let literals = self.parse_literal()?;
        self.next_equal(Keyword::Values.into())?;

        let vals = self.parse_value()?;

        let insert = Insert {
            table_name,
            literals,
            val: vals,
        };
        let token = Token::Insert(insert);
        Ok(token)
    }

    /**
     * update products set name = 'phone', price = 5 where id = 1 and name = 'ph'; 
     */
    pub fn parse_update(&mut self) -> Result<Token, Box<dyn Error>> {
        let table_name = self.next_variable()?;
        self.next_equal(Keyword::Set.into())?;

        let sets = self.parse_update_set()?;

        let mut uwhere = None;
        if let Ok(_) = self.next_equal(Keyword::Where.into()) {
            uwhere = self.parse_where()?;
        }

        let update = Update {
            table_name,
            columns: sets,
            uwhere,
        };
        let token = Token::Update(update);
        Ok(token)
    }

    pub fn parse_delete(&mut self) -> Result<Token, Box<dyn Error>> {
        let token = Token::Delete;
        Ok(token)
    }

    pub fn parse_alter(&mut self) -> Result<Token, Box<dyn Error>> {
        let token = Token::Alter;
        Ok(token)
    }

    pub fn parse_update_set(&mut self) -> Result<Vec<Entry>, Box<dyn Error>> {
        let mut sets: Vec<Entry> = Vec::new();
        loop {
            let key = self.next_variable()?;
            self.next_is_symbol('=')?;
            let value = self.next_variable()?;
            let entry = Entry::new(key, value);
            sets.push(entry);
            
            if let Err(_) = self.next_is_symbol(',') {
                return Ok(sets);
            }
        }
    }

    pub fn parse_table_columns(&mut self) -> Result<Vec<Column>, Box<dyn Error>> {
        let mut columns = Vec::new();

        while let Err(_) = self.next_is_symbol(')') {
            let column_name = self.next_variable()?;
            let data_type = self.next_variable()?;

            let mut column = Column {
                name: column_name,
                data_type: DataType::from_str(data_type.as_str())?,
                default: None,
                nullable: true,
                primary_key: false,
                comment: None,
            };

            while let Ok(word) = self.next_variable() {
                match word.as_str() {
                    "primary" => {
                        self.next_equal(Keyword::Key.into())?;
                        column.primary_key = true;
                    }
                    "not" => {
                        self.next_equal(Keyword::Null.into())?;
                        column.nullable = false;
                    }
                    "comment" => {
                        column.comment = Some(self.next_variable()?);
                    }
                    "default" => {
                        let default_str = self.next_variable()?;
                        let defalut_value = match column.data_type {
                            DataType::Bool => Value::Bool(default_str.parse()?),
                            DataType::Float => Value::Float(default_str.parse()?),
                            DataType::Int => Value::Int(default_str.parse()?),
                            DataType::Text => Value::Text(default_str),
                            DataType::TimeStamp => Value::Null,
                        };
                        column.default = Some(defalut_value);
                    }
                    "null" => continue,
                    _ => {
                        if let Ok(_) = self.next_is_symbol(',') {
                            break;
                        }
                        if let Ok(_) = self.peek_is_symbol(')') {
                            break;
                        }
                        return Err(CustomError::SqlParseError.into());
                    }
                };
            }

            columns.push(column);
        }

        Ok(columns)
    }

    pub fn equal_str(source: &str, target: &str) -> Result<(), Box<dyn Error>> {
        if source == target {
            return Ok(());
        }
        Err(CustomError::SqlParseError.into())
    }

    pub fn find_primary_key(columns: &Vec<Column>) -> Result<usize, Box<dyn Error>> {
        for (i, column) in columns.iter().enumerate() {
            if column.primary_key {
                return Ok(i);
            }
        }
        Err(CustomError::SqlParseError.into())
    }

    pub fn parse_limit(&mut self) -> Result<Option<(usize, usize)>, Box<dyn Error>> {
        let limit = self.next_variable()?.parse::<usize>()?;
        self.next_equal(Keyword::Offset.into())?;
        let offset = self.next_variable()?.parse::<usize>()?;
        Ok(Some((limit, offset)))
    }

    pub fn parse_group_by(&mut self) -> Result<Option<Vec<String>>, Box<dyn Error>> {
        self.next_equal(Keyword::By.into())?;
        let mut group_vec = Vec::new();
        loop {
            let variable = self.next_variable()?;
            group_vec.push(variable);

            if let Err(_) = self.peek_is_symbol(',') {
                return Ok(Some(group_vec));
            }
            self.next_is_symbol(',')?;
        }
    }

    pub fn parse_order_by(&mut self) -> Result<Option<Order>, Box<dyn Error>> {
        self.next_equal(Keyword::By.into())?;
        let literal = self.next_variable()?;
        let sort = self.next_variable()?;
        let order = Order::new(literal, sort);
        Ok(Some(order))
    }

    pub fn parse_where(&mut self) -> Result<Option<Vec<Entry>>, Box<dyn Error>> {
        let mut where_vec: Vec<Entry> = Vec::new();
        loop {
            let variable = self.next_variable()?;
            self.next_is_symbol('=')?;
            let value = self.next_variable()?;
            where_vec.push(Entry::new(variable, value));

            if let Err(_) = self.peek_is_symbol('a') {
                return Ok(Some(where_vec));
            }
            self.next_variable()?;
        }
    }

    fn parse_select_literal(&mut self) -> Result<SelectLiteral, Box<dyn Error>> {
        if let Ok(()) = self.peek_is_symbol('*') {
            self.next_is_symbol('*')?;
            return Ok(SelectLiteral::All);
        }
        let mut literals: Vec<String> = Vec::new();
        loop {
            let literal = self.next_variable()?;
            literals.push(literal);
            if let Err(_) = self.peek_is_symbol(',') {
                break;
            }
            self.next_is_symbol(',')?;
        }

        Ok(SelectLiteral::Literal(literals))
    }

    pub fn parse_value(&mut self) -> Result<Vec<Vec<String>>, Box<dyn Error>> {
        self.next_is_symbol('(')?;
        let mut valss: Vec<Vec<String>> = Vec::new();

        let mut vals: Vec<String> = Vec::new();
        loop {
            let val = self.next_variable()?;
            vals.push(val);
            if let Ok(_) = self.peek_is_symbol(')') {
                self.next_is_symbol(')')?;
                break;
            }
            self.next_is_symbol(',')?;
        }
        valss.push(vals);

        Ok(valss)
    }

    pub fn parse_literal(&mut self) -> Result<Option<Vec<String>>, Box<dyn Error>> {
        if let Err(_) = self.next_is_symbol('(') {
            return Ok(None);
        }

        let mut literals: Vec<String> = Vec::new();
        loop {
            let literal = self.next_variable()?;
            literals.push(literal);
            if let Ok(_) = self.peek_is_symbol(')') {
                self.next_is_symbol(')')?;
                break;
            }
            self.next_is_symbol(',')?;
        }

        Ok(Some(literals))
    }

    pub fn peek_is_symbol(&mut self, c: char) -> Result<(), Box<dyn Error>> {
        self.next_is_whitespace()?;

        if let Some(&peek_c) = self.code.peek() {
            if c == peek_c {
                return Ok(());
            }
        }
        Err(CustomError::SqlParseError.into())
    }

    pub fn next_is_symbol(&mut self, c: char) -> Result<(), Box<dyn Error>> {
        self.next_is_whitespace()?;

        if let Some(_) = self.code.next_if(|&n| n == c) {
            return Ok(());
        }
        Err(CustomError::SqlParseError.into())
    }

    pub fn next_is_whitespace(&mut self) -> Result<(), Box<dyn Error>> {
        while let Some(&c) = self.code.peek() {
            if c.is_whitespace() || '\r' == c || '\n' == c {
                let _ = self.code.next();
            } else {
                break;
            }
        }
        Ok(())
    }

    pub fn is_alphabetic_underline<F>(&mut self, f: F) -> Option<String>
    where
        F: Fn(char) -> bool,
    {
        let mut literal = String::new();
        while let Some(&c) = self.code.peek() {
            if !c.is_alphabetic() && f(c) {
                return Some(literal);
            }
            self.code.next();
            literal.push(c);
        }
        None
    }

    pub fn next_equal(&mut self, target: &str) -> Result<(), Box<dyn Error>> {
        let source = self.next_variable()?;
        Self::equal_str(&source, target)
    }

    fn next_keyword<F>(&mut self, f: F) -> Option<String>
    where
        F: Fn(char) -> bool,
    {
        if let None = self.code.next() {
            return None;
        }
        let mut keyword = String::new();
        while let Some(c) = self.code.next() {
            if f(c) {
                break;
            }
            keyword.push(c);
        }
        return Some(keyword);
    }

    fn next_number(&mut self) -> Option<String> {
        let mut keyword = String::new();
        let mut is_use_point = false;
        while let Some(&peek_c) = self.code.peek() {
            if (!peek_c.is_digit(10) && '.' != peek_c) || (is_use_point && '.' == peek_c) {
                break;
            }
            if '.' == peek_c {
                is_use_point = true;
            }
            keyword.push(self.code.next().unwrap());
        }
        return Some(keyword);
    }

    fn next_variable(&mut self) -> Result<String, Box<dyn Error>> {
        let keyword = self.next();
        if let None = keyword {
            return Err(CustomError::SqlParseError.into());
        }
        Ok(keyword.unwrap().to_lowercase())
    }

}

impl<'a> Iterator for Lexer<'a> {
    type Item = String;

    fn next(&mut self) -> Option<Self::Item> {
        let _ = self.next_is_whitespace();

        while let Some(&c) = self.code.peek() {
            match c {
                '\'' => return self.next_keyword(|c| '\'' == c),
                '`' => return self.next_keyword(|c| '`' == c),
                '"' => return self.next_keyword(|c| '"' == c),
                c if c.is_digit(10) => return self.next_number(),
                c if c.is_alphabetic() => return self.is_alphabetic_underline(|c| '_' != c),
                c => return Some(c.to_string()),
            }
        }

        None
    }
}

#[cfg(test)]
mod test {

    use super::Lexer;
    use std::{error::Error, iter::Peekable};

    #[test]
    fn test_create_table() -> Result<(), Box<dyn Error>> {
        let mut lexer = Lexer::new(
            "CREATE TABLE products (
        product_id INT NOT NULL PRIMARY key COMMENT '唯一id',
        name TEXT NOT NULL,
        price FLOAT,
        stock_quantity INT DEFAULT 0
        );",
        );
        let token = lexer.parse()?;

        println!("token: {:#?}", token);
        Ok(())
    }

    #[test]
    fn test_insert() -> Result<(), Box<dyn Error>> {
        let mut lexer = Lexer::new("insert into products values (1, 'hello', 3.0, 1); ");
        let token = lexer.parse()?;

        println!("token: {:#?}", token);
        Ok(())
    }

    #[test]
    fn test_insert2() -> Result<(), Box<dyn Error>> {
        let mut lexer = Lexer::new("insert into products (product_id, name, price, stock_quantity) values (2, 'hello', 3.0, 1); ");
        let token = lexer.parse()?;

        println!("token: {:#?}", token);
        Ok(())
    }

    #[test]
    fn test_select() -> Result<(), Box<dyn Error>> {
        let mut lexer = Lexer::new("select * from products;");
        let token = lexer.parse()?;

        println!("token: {:#?}", token);
        Ok(())
    }

    #[test]
    fn test_select2() -> Result<(), Box<dyn Error>> {
        let mut lexer = Lexer::new("select * from products where id = 1 and name = 'phone'; ");
        let token = lexer.parse()?;

        println!("token: {:#?}", token);
        Ok(())
    }

    #[test]
    fn test_select3() -> Result<(), Box<dyn Error>> {
        let mut lexer = Lexer::new("select `id`, name, price from products where name = 'phone' and id = 1 group by name order by name asc limit 10 offset 0; ");
        let token = lexer.parse()?;

        println!("token: {:#?}", token);
        Ok(())
    }
    #[test]
    fn test_update() -> Result<(), Box<dyn Error>> {
        let mut lexer = Lexer::new("update products set name = 'phone', price = 5 ;");
        let token = lexer.parse()?;

        println!("token: {:#?}", token);
        Ok(())
    }
    
    #[test]
    fn test_update2() -> Result<(), Box<dyn Error>> {
        let mut lexer = Lexer::new("update products set name = 'phone', price = 5 where id = 1 and name = 'ph';");
        let token = lexer.parse()?;

        println!("token: {:#?}", token);
        Ok(())
    }

    #[test]
    fn test_iter() -> Result<(), Box<dyn Error>> {
        struct Parser<'a> {
            pub lexer: Peekable<Lexer<'a>>,
        }

        impl<'a> Iterator for Parser<'a> {
            type Item = String;

            fn next(&mut self) -> std::option::Option<<Self as Iterator>::Item> {
                self.next()
            }
        }

        impl<'a> Parser<'a> {
            fn peek(&mut self) -> Option<&String> {
                self.lexer.peek()
            }
        }

        let mut lexer = Lexer::new("update products set name = 'phone', price = 5 where id = 1 and name = 'ph';");
        let parser = Parser {
            lexer: lexer.peekable(), 
        };

        //while let Some(key) = parser.p
        
        
        Ok(())
    }
}
